* Nicolai Petrovsky
* nicolai.petrovsky@uky.edu

* This do-file runs the 
* analysis reported in the Petrovsky and Avellaneda (2014) 
* article "Mayoral Public Sector Work Experience and 
* Tax Collection Performance in Colombian Local Governments." 
* International Public Management Journal 17 (2): 145-173. 

* Stata version 11.2
* last modified July 7, 2014

* Data file required:
* PetrovskyAvellaneda2014IPMJ.dta

* Please copy this do-file and the data file into a
* directory on your hard drive.
* Then, please use Stata's command
* cd
* to change to that directory.
* Now you can run this do-file.

* How to find the result(s) you are interested in: 

* You can find the results for the items that most interest 
* you in the following way: 
* After running this do-file, view the log-file 
* (PetrovskyAvellaneda2014IPMJ.smcl) and search within it 
* for "Page 161" if you would like to see a result from 
* page 161 in the article; 
* same for all other pages with results.  
* (In order to search within the log-file, click the 
* search button on the top left-hand side of the Stata viewer.)

clear
clear matrix
version 11.2
set more off
set memory 100m
set scheme s1mono

* Open a log and allow for replacement
log using PetrovskyAvellaneda2014IPMJ.smcl, replace

* Open data
use PetrovskyAvellaneda2014IPMJ.dta

* Development index
gen DEVINDEX = (percenthomeselectricity_x + percentliterate_x + percenthomessewer_x) / 3

* Page 157: "Cronbach's alpha for our development index 
* is 0.64."
alpha percenthomeselectricity_x percentliterate_x percenthomessewer_x

* Define a shorthand expression for the vector of 
* right-hand side variables that all specifications
* have in common: 
local controls "mayoreducyrs mayorpubsecexpyears findirectorpubsecexpyears midmanper1Kcap mayor_con mayorgovernorpartymatch extfundspercap DEVINDEX ITD exintcom city100Kplus percruralproperties propertiesperpop displaced y2006 y2007"

* Generate common estimation samples
quietly reg DV1 L.DV1 DV2 L.DV2 avaluopercap `controls'
gen estimationsample = 1 if e(sample)

* Find out how many municipalities are in the common estimation sample: 
sort year
codebook codigo if estimationsample == 1
sort codigo year
save, replace

* Page 159, Table 1: Summary Statistics
sum rawDV1 DV1 DV2 avaluopercap `controls' y2008 /*
*/ if estimationsample == 1
mkcorr rawDV1 DV1 DV2 avaluopercap `controls' y2008 /*
*/ if estimationsample == 1, log(IPMJcoSummaryStatistics.txt) replace casewise means nocorr mdec(2) 

* Page 161, Table 2, first numerical column
reg DV1 L.DV1 avaluopercap `controls' /*
*/ if estimationsample == 1, cluster(codigo)
outreg2 using Column1.txt, tstat bdec(4) tdec(2) word replace

* Page 161, Table 2, second numerical column
reg DV2 L.DV2 `controls' if estimationsample == 1, /*
*/ cluster(codigo)
outreg2 using Column2.txt, tstat bdec(4) tdec(2) word replace

* Page 167, endnote 20: "We considered the possibility that there 
* might be an optimal number of middle managers per capita, such 
* that exceeding this number would lead to a reduction instead 
* of an increase in tax collection (implying an inverse U-shaped 
* relationship). Therefore, we also estimated both models from 
* Table 2 with an additional variable included: the squared value 
* of the number of middle managers per 1,000 population. In these 
* two specifications, neither the base nor the squared term of 
* middle managers per 1,000 population was statistically 
* different from zero, although the null hypothesis of a joint 
* test of both variables was rejected in both specifications."

local controls2 "mayoreducyrs mayorpubsecexpyears findirectorpubsecexpyears midmanper1Kcap midman2 mayor_con mayorgovernorpartymatch extfundspercap DEVINDEX ITD exintcom city100Kplus percruralproperties propertiesperpop displaced y2006 y2007"

* extra Table 2, first numerical column
reg DV1 L.DV1 avaluopercap `controls2' /*
*/ if estimationsample == 1, cluster(codigo)
outreg2 using extra_squaredMM_Column1.txt, /*
*/ tstat bdec(4) tdec(2) word replace
test midmanper1Kcap midman2

* extra Table 2, second numerical column
reg DV2 L.DV2 `controls2' if estimationsample == 1, /*
*/ cluster(codigo)
outreg2 using extra_squaredMM_Column2.txt, /*
*/ tstat bdec(4) tdec(2) word replace
test midmanper1Kcap midman2

drop DEVINDEX
drop estimationsample
save, replace
clear
clear matrix
log close
exit

* You can now view a record of all operations and results in 
* the PetrovskyAvellaneda2014IPMJ.smcl file. 
